﻿/* 
* Copyright (c) 2009, SCUT 06级计科2班软件工程第6小组
* All rights reserved. 
*
* 作    者：蔡伟林
* 完成日期：
*  
* 摘    要：数据访问层的SQL SERVER实现
*
* 备    注：
*
*/
using System;  
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using SE6;

public class SqlDataAccess : IDataAccess
{
#region 用户相关数据访问层实现
	//添加用户至SQL数据库
    public Boolean Insert(User user)
    {
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
			String commandString =
				"insert into "+
					"Users("+
						"name,email,firstHashedPassword,hashedPassword,encryptedDESKey," +
						"question,hashedAnswer,answerEncryptedDESKey," +
						"point,roles,registerTime,lastLoginTime,loginTimes) " +
					"values("+
						"@name,@email,@firstHashedPassword,@hashedPassword,@encryptedDESKey," +
						"@question,@hashedAnswer,@answerEncryptedDESKey," +
						"@point,@roles,@registerTime,@lastLoginTime,@loginTimes); " +
				"select SCOPE_IDENTITY()";
            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            cmd.Parameters.AddWithValue("@name", user.Name);
			cmd.Parameters.AddWithValue("@email", user.Email);
			cmd.Parameters.AddWithValue("@firstHashedPassword", user.FirstHashedPassword);
            cmd.Parameters.AddWithValue("@hashedPassword", user.HashedPassword);
			cmd.Parameters.AddWithValue("@encryptedDESKey", user.EncryptedDESKey);

			cmd.Parameters.AddWithValue("@question", user.Question);
			cmd.Parameters.AddWithValue("@hashedAnswer", user.HashedAnswer);
			cmd.Parameters.AddWithValue("@answerEncryptedDESKey", user.AnswerEncryptedDESKey);

            cmd.Parameters.AddWithValue("@point", user.Point);
			cmd.Parameters.AddWithValue("@roles", user.Roles);
            cmd.Parameters.AddWithValue("@registerTime", user.RegisterTime);
            cmd.Parameters.AddWithValue("@lastLoginTime", user.LastLoginTime);
            cmd.Parameters.AddWithValue("@loginTimes", user.LoginTimes);

			user.ID = Convert.ToInt32(cmd.ExecuteScalar());
			connection.Close();
			return true;
        }
        catch (System.Exception e)
        {
            connection.Close();
            return false;
        }
    }


    //更新用户信息，且user.ID有效，更新user所有相关信息(ID\Name除外)
	public Boolean Update(User user)
    {
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
			String commandString =
				"update  Users set "+
					"name=@name,"+
					"email=@email,"+
					"hashedPassword=@hashedPassword,"+
					"encryptedDESKey=@encryptedDESKey,"+
					"question=@question,"+
					"hashedAnswer=@hashedAnswer,"+
					"answerEncryptedDESKey=@answerEncryptedDESKey,"+
					"point=@point,"+
					"roles=@roles,"+
					"lastLoginTime=@lastLoginTime,"+
					"loginTimes=@loginTimes "+
					"where id=@id;";
            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
			cmd.Parameters.AddWithValue("@id", user.ID);

			cmd.Parameters.AddWithValue("@name", user.Name);
			cmd.Parameters.AddWithValue("@email", user.Email);
			cmd.Parameters.AddWithValue("@hashedPassword", user.HashedPassword);
			cmd.Parameters.AddWithValue("@encryptedDESKey", user.EncryptedDESKey);

			cmd.Parameters.AddWithValue("@question", user.Question);
			cmd.Parameters.AddWithValue("@hashedAnswer", user.HashedAnswer);
			cmd.Parameters.AddWithValue("@answerEncryptedDESKey", user.AnswerEncryptedDESKey);

			cmd.Parameters.AddWithValue("@point", user.Point);
			cmd.Parameters.AddWithValue("@roles", user.Roles);
			cmd.Parameters.AddWithValue("@lastLoginTime", user.LastLoginTime);
			cmd.Parameters.AddWithValue("@loginTimes", user.LoginTimes);

			Int32 effectRow = cmd.ExecuteNonQuery();
			connection.Close();
            return effectRow>0;
        }
        catch (System.Exception e)
        {
            connection.Close();
            return false;
        }
    }


	//删除ID为id的用户
    public Boolean DeleteUser(Int32 id)
    {
		return DeleteByID("Users", id);
    }

	//获取用户名为name的全部用户信息,若不存在该用户返回null
    public User GetUser(String name)
    {
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
			String commandString = "select * from Users where name=@name";
            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            cmd.Parameters.AddWithValue("@name", name);
            SqlDataReader dr = cmd.ExecuteReader();
			User newUser = null;
            if (dr.Read())
            {
				newUser = new User(
					dr.GetInt32(0),//id
					dr.GetString(1),//name
					dr.GetString(2),//email
					dr.GetSqlBytes(3).Value,// firstHashedPassword
					dr.GetSqlBytes(4).Value,// hashedPassword
					dr.GetSqlBytes(5).Value,// encryptedDESKey
					dr.GetString(6),//question
					dr.GetSqlBytes(7).Value,//hashedAnswer
					dr.GetSqlBytes(8).Value,//answerEncryptedDESKey,
					dr.GetInt32(9),//point
					(Role)dr.GetInt32(10),//roles
					dr.GetDateTime(11).ToString(),//registerTime
					dr.GetDateTime(12).ToString(),//String lastLoginTime
					dr.GetInt32(13)//loginTimes
                    );
            }
            connection.Close();
            return newUser;
        }
        catch (System.Exception e)
        {
            connection.Close();
            return null;
        }
    }
	//获得用户总数
	public Int32 GetUsersCount()
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString = "select count(id) from Users";
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			Object cnt = cmd.ExecuteScalar();
			connection.Close();
			return Convert.ToInt32(cnt);
		}
		catch (System.Exception e)
		{
			connection.Close();
			return 0;
		}
	}
	//返回从第start开始的最多maxCount个用户的信息
	public List<User> GetUsers(Int32 start, Int32 maxCount)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString =
				"select top " + maxCount + " *  from Users " +
				"where id not in (select top " + start + " id from Users order by id) order by id ";
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			SqlDataReader dr = cmd.ExecuteReader();
			List<User> usrList = null;
			if (dr.Read())
			{
				usrList = new List<User>();
				do
				{
					User newUser = new User(
						dr.GetInt32(0),//id
						dr.GetString(1),//name
						dr.GetString(2),//email
						dr.GetSqlBytes(3).Value,// firstHashedPassword
						dr.GetSqlBytes(4).Value,// hashedPassword
						dr.GetSqlBytes(5).Value,// encryptedDESKey
						dr.GetString(6),//question
						dr.GetSqlBytes(7).Value,//hashedAnswer
						dr.GetSqlBytes(8).Value,//answerEncryptedDESKey,
						dr.GetInt32(9),//point
						(Role)dr.GetInt32(10),//roles
						dr.GetDateTime(11).ToString(),//registerTime
						dr.GetDateTime(12).ToString(),//String lastLoginTime
						dr.GetInt32(13)//loginTimes
						);
					usrList.Add(newUser);
				} while (dr.Read());
			}
			connection.Close();
			return usrList;
		}
		catch (System.Exception e)
		{
			connection.Close();
			return null;
		}
	}
	public User GetUser(Int32 id)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString = "select * from Users where id=" + id;
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			SqlDataReader dr = cmd.ExecuteReader();
			User newUser = null;
			if (dr.Read())
			{
				newUser = new User(
					dr.GetInt32(0),//id
					dr.GetString(1),//name
					dr.GetString(2),//email
					dr.GetSqlBytes(3).Value,// firstHashedPassword
					dr.GetSqlBytes(4).Value,// hashedPassword
					dr.GetSqlBytes(5).Value,// encryptedDESKey
					dr.GetString(6),//question
					dr.GetSqlBytes(7).Value,//hashedAnswer
					dr.GetSqlBytes(8).Value,//answerEncryptedDESKey,
					dr.GetInt32(9),//point
					(Role)dr.GetInt32(10),//roles
					dr.GetDateTime(11).ToString(),//registerTime
					dr.GetDateTime(12).ToString(),//String lastLoginTime
					dr.GetInt32(13)//loginTimes
					);
			}
			connection.Close();
			return newUser;
		}
		catch (System.Exception e)
		{
			connection.Close();
			return null;
		}
	}

	//获得用户名为name的用户ID，若不存在返回0
	public Int32 GetUserID(String name)
    {
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString = 	"select id from Users where name = @name";
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			cmd.Parameters.AddWithValue("@name", name);
			Object ret = cmd.ExecuteScalar();
			connection.Close();
			return ret == null ? 0 : Convert.ToInt32(ret);
		}
		catch(Exception)
		{
			connection.Close();
			return 0;
		}
	}
#endregion

#region 档案管理数据访问层实现
	//添加档案
	public Boolean Insert(Archive ar)
	{
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
//             String archivetablename;
//             archivetablename = ar.SecurityLevel == ArchiveSecurityLevel.Private ? "ImportantArchives" : "NormalArchives";

            String commandString =
                "insert into Archives(" + 
                        "ownerID,name,tag0,tag1,tag2,tag3,tag4," +
                        "point,dpt,cont,type,sl,cat,ct,vc,dc,size,state) " +
                    "values("+
						"@ownerID,@name,@tag0,@tag1,@tag2,@tag3,@tag4," +
						 "@point,@dpt,@cont,@type,@sl,@cat,@ct,@vc,@dc,@size,@state);" +
                "select SCOPE_IDENTITY()";
            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
			cmd.Parameters.AddWithValue("@ownerID", ar.OwnerID);
            cmd.Parameters.AddWithValue("@name", ar.Name);
			cmd.Parameters.AddWithValue("@tag0", ar.Keywords[0]);
			cmd.Parameters.AddWithValue("@tag1", ar.Keywords[1]);
			cmd.Parameters.AddWithValue("@tag2", ar.Keywords[2]);
			cmd.Parameters.AddWithValue("@tag3", ar.Keywords[3]);
			cmd.Parameters.AddWithValue("@tag4", ar.Keywords[4]);

			cmd.Parameters.AddWithValue("@point", ar.Point);
			cmd.Parameters.AddWithValue("@dpt", ar.Description);
			cmd.Parameters.AddWithValue("@cont", ar.Content);
			cmd.Parameters.AddWithValue("@type", ar.Type);
            cmd.Parameters.AddWithValue("@sl", ar.SecurityLevel);
            cmd.Parameters.AddWithValue("@cat", ar.Category);
            cmd.Parameters.AddWithValue("@ct", ar.CreateTime);
            cmd.Parameters.AddWithValue("@vc", ar.ViewCount);
            cmd.Parameters.AddWithValue("@dc", ar.DownloadCount);
			cmd.Parameters.AddWithValue("@size", ar.Size);
			cmd.Parameters.AddWithValue("@state", ar.State);

			ar.ID = Convert.ToInt32(cmd.ExecuteScalar());
            connection.Close();
            return true;
        }
        catch (System.Exception e)
        {
            connection.Close();
            return false;
        }
	}
	//更新档案信息，不包括档案内容本身！
	public Boolean Update(Archive ar)
    {
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
            //             String archivetablename;
            //             archivetablename = ar.SecurityLevel == ArchiveSecurityLevel.Private ? "ImportantArchives" : "NormalArchives";

            String commandString =
                "update Archives set " +
                        " ownerID="+ar.OwnerID+","+
                        "name=@name,"+
                        "tag0=@tag0,"+
                        "tag1=@tag1,"+
                        "tag2=@tag2,"+
                        "tag3=@tag3,"+
                        "tag4=@tag4,"+
                        "point=@point,"+
                        "dpt=@dpt,"+
                        "type=@type,"+
                        "sl=@sl,"+
                        "cat=@cat,"+
                        "ct=@ct,"+
                        "vc=@vc,"+
                        "dc=@dc,"+
                        "size=@size,"+
                        "state=@state where id="+ar.ID;
            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            cmd.Parameters.AddWithValue("@name", ar.Name);
            cmd.Parameters.AddWithValue("@tag0", ar.Keywords[0]);
            cmd.Parameters.AddWithValue("@tag1", ar.Keywords[1]);
            cmd.Parameters.AddWithValue("@tag2", ar.Keywords[2]);
            cmd.Parameters.AddWithValue("@tag3", ar.Keywords[3]);
            cmd.Parameters.AddWithValue("@tag4", ar.Keywords[4]);

            cmd.Parameters.AddWithValue("@point", ar.Point);
            cmd.Parameters.AddWithValue("@dpt", ar.Description);
            cmd.Parameters.AddWithValue("@type", ar.Type);
            cmd.Parameters.AddWithValue("@sl", ar.SecurityLevel);
            cmd.Parameters.AddWithValue("@cat", ar.Category);
            cmd.Parameters.AddWithValue("@ct", ar.CreateTime);
            cmd.Parameters.AddWithValue("@vc", ar.ViewCount);
            cmd.Parameters.AddWithValue("@dc", ar.DownloadCount);
            cmd.Parameters.AddWithValue("@size", ar.Size);
            cmd.Parameters.AddWithValue("@state", ar.State);

            Int32 effectRow = cmd.ExecuteNonQuery();
            connection.Close();
            return effectRow > 0;
        }
        catch (System.Exception e)
        {
            connection.Close();
            return false;
        }

	}
	//删除ID为ar.ID的档案
	public Boolean DeleteArchive(Int32 id)
	{
		return DeleteByID("Archives", id);;
	}
	//获得档案描述，不包括档案内容本身
	public Archive GetArchiveDescription(Int32 id)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			//这里没有用事物，ViewCount未必如此重要
			String commandString =
				"update Archives set vc=vc+1 where id="+id+
				";select id,ownerID,name,tag0,tag1,tag2,tag3,tag4," +
                      "point,dpt,type,sl,cat,ct,vc,dc,size,state from Archives where id="+id;
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			SqlDataReader dr = cmd.ExecuteReader();
			Archive ar = null;
			if (dr.Read())
			{
					ar = new Archive(
					dr.GetInt32(0),//id
					dr.GetInt32(1),//ownerID
					dr.GetString(2),//name
					new String[]{ //keywords
						dr.GetString(3),//tag0
						dr.GetString(4),//tag1
						dr.GetString(5),//tag2
						dr.GetString(6),//tag3
						dr.GetString(7),//tag4
					},
					dr.GetInt32(8),//point
					dr.GetString(9),//description
					(ArchiveType)dr.GetInt32(10),//type
					(ArchiveSecurityLevel)dr.GetInt32(11),//ArchiveSecurityLevel
					dr.GetString(12),//category
					dr.GetDateTime(13).ToString(),//createTime
					dr.GetInt32(14),//viewCount
					dr.GetInt32(15),//downloadCount
					dr.GetInt32(16),//size
					null,//dr.GetSqlBinary(10).Value,//content
					(ArchiveState)dr.GetInt32(17)   //state
					);
			}
			connection.Close();
			return ar;
		}
		catch (System.Exception e)
		{
			connection.Close();
			return null;
		}
	}
	//仅获取ID为id的档案档案内容、价值分，并在usrID的用户减去相应档案价值分
	//即用户下载该档案
	public Byte[] GetArchiveContent(Int32 id, Int32 usrID, Int32 point)
	{
		using (SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING))
		{
			connection.Open();
			SqlCommand cmd = connection.CreateCommand();
			SqlTransaction transaction = connection.BeginTransaction();
			cmd.Transaction = transaction;
			try
			{
				String commandText =
// 					"declare @point int;"+
// 					"select @point=point from Archives where id="+id+";"
					"update Archives set dc=dc+1 where id="+id+
					";if 0!="+usrID+" update Users set point=point-"+point+" where id="+usrID+
					";select cont from Archives where id=" + id;
				cmd.CommandText = commandText;
				SqlDataReader dr = cmd.ExecuteReader();
				Byte[] ar = null;
				if (dr.Read())
					ar = dr.GetSqlBinary(0).Value;
				dr.Close();
				transaction.Commit();
				return ar;
			}
			catch (System.Exception e)
			{
				transaction.Rollback();
				return null;
			}
		}
		return null;
	}
	//	1、获得指定用ID的所有档案目录及其相应档案数（用于：用户查看并管理自己的档案）
	public List<ArchiveCategory> GetMyArchiveCategories(Int32 myID)
    {
        List<ArchiveCategory> list = null;
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
            String commandString =
                "select cat,count(*) from Archives "+
                "where ownerID=@ownerID group by cat";
                        SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            cmd.Parameters.AddWithValue("@ownerID", myID);
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                list = new List<ArchiveCategory>();
                do
                {
                    list.Add(new ArchiveCategory(dr.GetString(0), dr.GetInt32(1)));
                } while (dr.Read()) ;
            }
            connection.Close();
            return list;
        }
        catch (System.Exception e)
        {
            connection.Close();
            return null;
        }
	}
	//	2、对指定用户ID以及档案分类category的所有档案以id排序，返回从第start开始的最多maxCount个档案
	public List<Archive> GetMyArchives(
		Int32 myID, String category, Int32 start, Int32 maxCount)
    {
        List<Archive> list = null;
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
            String commandString =
                "select top " + maxCount + " id,ownerID,name,tag0,tag1,tag2,tag3,tag4," +
                        "point,dpt,type,sl,cat,ct,vc,dc,size,state from Archives " +
                "where ownerID = " + myID + " and cat = @cat and id not in" +
                "(select top " + start + " id from Archives " +
					"where ownerID=" + myID + " and cat = @cat order by id) order by id";

            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            cmd.Parameters.AddWithValue("@cat", category);
            SqlDataReader dr = cmd.ExecuteReader();

            Archive ar = null;
            String[] tempArray = null;
            if (dr.Read())
            {
                list = new List<Archive>();
                do
                {
					Archive curAr = new Archive(
						dr.GetInt32(0),//id
						dr.GetInt32(1),//ownerID
						dr.GetString(2),//name
						new String[]{ //keywords
							dr.GetString(3),//tag0
							dr.GetString(4),//tag1
							dr.GetString(5),//tag2
							dr.GetString(6),//tag3
							dr.GetString(7),//tag4
						},
						dr.GetInt32(8),//point
						dr.GetString(9),//description
						(ArchiveType)dr.GetInt32(10),//type
						(ArchiveSecurityLevel)dr.GetInt32(11),//ArchiveSecurityLevel
						dr.GetString(12),//category
						dr.GetDateTime(13).ToString(),//createTime
						dr.GetInt32(14),//viewCount
						dr.GetInt32(15),//downloadCount
						dr.GetInt32(16),//size
						null,//dr.GetSqlBinary(10).Value,//content
						(ArchiveState)dr.GetInt32(17)   //state
						);
					list.Add(curAr);
                } while (dr.Read());
            }
            connection.Close();
            return list;
		}
        catch (System.Exception e)
        {
            connection.Close();
            return null;
        }
	}
	//3、检索与keyword有关（档案【标题】以及【描述】以及【分类】）的所有档案，且档案安全级别不大于maxLevel，
	//		，返回从第start开始的最多maxCount个档案，（不包括档案内容本身）
	// a.匿名用户搜索,maxLevel=ArchiveSeruityLevel.Public
	// b.普通注册用户搜索，maxLevel=ArchiveSeruityLevel.Share
	// c.管理员用户搜索（审核档案），maxLevel=ArchiveSeruityLevel.Safe
	public List<Archive> GetArchives(
        String keyword,ArchiveType type, ArchiveSecurityLevel maxLevel, Int32 start, Int32 maxCount)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String typeString = (type != ArchiveType.All) ? ("  type =" + (Int32)type + " and ") : "";
            String commandString = "select top "+maxCount+
                " id,ownerID,name,tag0,tag1,tag2,tag3,tag4," +
                        "point,dpt,type,sl,cat,ct,vc,dc,size,state from Archives " +
                "where " + typeString + " sl<= " + (Int32)maxLevel +" and id not in (" +
                "select top "+start+" id from Archives " +
                "where " + typeString + " sl<= " + (Int32)maxLevel + " and (name like '%'+@keywords+'%' or " +
                "dpt like '%'+@keywords+'%' or " +
                "tag1 = @keywords or " +
                "tag2 = @keywords or " +
                "tag3 = @keywords or " +
                "tag4 = @keywords or " +
                "tag0 = @keywords " +
                " )  order by ct)" +
                "and (name like '%'+@keywords+'%' or " +
                "dpt like '%'+@keywords+'%' or " +
                "tag1 = @keywords or " +
                "tag2 = @keywords or " +
                "tag3 = @keywords or " +
                "tag4 = @keywords or " +
				"tag0 = @keywords)  order by ct";
            
			SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            //cmd.Parameters.AddWithValue("@type", type);
            cmd.Parameters.AddWithValue("@keywords", keyword);
            //cmd.Parameters.AddWithValue("@maxLevel", (int)maxLevel);
          //  cmd.Parameters.AddWithValue("@start", start);
          //  cmd.Parameters.AddWithValue("@maxCount", maxCount);
			SqlDataReader dr = cmd.ExecuteReader();
			List<Archive> archives = null;
			if (dr.Read())
			{
				archives = new List<Archive>();
				do
				{					
					Archive curAr = new Archive(
						dr.GetInt32(0),//id
						dr.GetInt32(1),//ownerID
						dr.GetString(2),//name
						new String[]{ //keywords
							dr.GetString(3),//tag0
							dr.GetString(4),//tag1
							dr.GetString(5),//tag2
							dr.GetString(6),//tag3
							dr.GetString(7),//tag4
						},
						dr.GetInt32(8),//point
						dr.GetString(9),//description
						(ArchiveType)dr.GetInt32(10),//type
						(ArchiveSecurityLevel)dr.GetInt32(11),//ArchiveSecurityLevel
						dr.GetString(12),//category
						dr.GetDateTime(13).ToString(),//createTime
						dr.GetInt32(14),//viewCount
						dr.GetInt32(15),//downloadCount
						dr.GetInt32(16),//size
						null,//dr.GetSqlBinary(10).Value,//content
                        (ArchiveState)dr.GetInt32(17)   //state
						);
					archives.Add(curAr);
				} while (dr.Read());
			}
			connection.Close();
			return archives;
		}
		catch (System.Exception e)
		{
			connection.Close();
			return null;
		}
	}
	public Int32 GetMatchedArchivesCount(String keyword, ArchiveType type, ArchiveSecurityLevel maxLevel)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String typeCondition = (type != ArchiveType.All) ? ("type =" + (Int32)type + " and") : "";
			String commandString = 
				"select count(id) from Archives " +
				"where " + typeCondition + " sl<= " + (Int32)maxLevel + " and (name like '%'+@keywords+'%' or " +
				"dpt like '%'+@keywords+'%' or " +
				"tag1 = @keywords or " +
				"tag2 = @keywords or " +
				"tag3 = @keywords or " +
				"tag4 = @keywords or " +
				"tag0 = @keywords " +
				" ) " ;
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			cmd.Parameters.AddWithValue("@keywords", keyword);
			Object count = cmd.ExecuteScalar();
			connection.Close();
			return Convert.ToInt32(count);
		}
		catch (System.Exception e)
		{
			connection.Close();
			return 0;
		}		
	}

    //对指定用ID的所有档案以id排序，返回从第start开始的最多maxCount个档案
    public List<Archive> GetArchives(Int32 myID, ArchiveSecurityLevel maxLevel, Int32 start, Int32 maxCount)
    {
        List<Archive> list = null;
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
            String commandString =
                "select top " + maxCount + " id,ownerID,name,tag0,tag1,tag2,tag3,tag4," +
                        "point,dpt,type,sl,cat,ct,vc,dc,size,state from Archives " +
                "where ownerID = " + myID + "  and id not in" +
                "(select top " + start + " id from Archives " +
                    "where ownerID=" + myID ;

            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                list = new List<Archive>();
                do
                {
                    Archive curAr = new Archive(
                        dr.GetInt32(0),//id
                        dr.GetInt32(1),//ownerID
                        dr.GetString(2),//name
                        new String[]{ //keywords
							dr.GetString(3),//tag0
							dr.GetString(4),//tag1
							dr.GetString(5),//tag2
							dr.GetString(6),//tag3
							dr.GetString(7),//tag4
						},
                        dr.GetInt32(8),//point
                        dr.GetString(9),//description
                        (ArchiveType)dr.GetInt32(10),//type
                        (ArchiveSecurityLevel)dr.GetInt32(11),//ArchiveSecurityLevel
                        dr.GetString(12),//category
                        dr.GetDateTime(13).ToString(),//createTime
                        dr.GetInt32(14),//viewCount
                        dr.GetInt32(15),//downloadCount
                        dr.GetInt32(16),//size
                        null,//dr.GetSqlBinary(10).Value,//content
                        (ArchiveState)dr.GetInt32(17)   //state
                        );
                    list.Add(curAr);
                } while (dr.Read());
            }
            connection.Close();
            return list;
        }
        catch (System.Exception )
        {
            connection.Close();
            return null;
        }
    }

    //返回myID所有的，最高等级不高于maxLevel的档案个数
    public Int32 GetMatchedArchivesCount(Int32 myID, ArchiveSecurityLevel maxLevel)
    {
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {

            String commandString =
                "select count(id) from Archives " +
                "where  sl<= " + (Int32)maxLevel  + ")" ;
            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            Object count = cmd.ExecuteScalar();
            connection.Close();
            return Convert.ToInt32(count);
        }
        catch (System.Exception e)
        {
            connection.Close();
            return 0;
        }		
    }

#endregion

#region 留言管理,(已换成BBS)
// 	//添加留言
// 	public Boolean Insert(LeaveWord leaveWord)
// 	{
// 		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
// 		try
// 		{
// 			String commandString =
// 				"insert into " +
// 					"LeaveWords(author,title,content,createTime) " +
// 					"values(@author,@title,@content,@createTime);" +
// 				"select SCOPE_IDENTITY()";
// 			SqlCommand cmd = new SqlCommand(commandString, connection);
// 			connection.Open();
// 			cmd.Parameters.AddWithValue("@author", leaveWord.Author);
// 			cmd.Parameters.AddWithValue("@title", leaveWord.Title);
// 			cmd.Parameters.AddWithValue("@content", leaveWord.Content);
// 			cmd.Parameters.AddWithValue("@createTime", leaveWord.CreateTime);
// 
// 			leaveWord.ID = Convert.ToInt32(cmd.ExecuteScalar());
// 			connection.Close();
// 			return true;
// 		}
// 		catch (System.Exception e)
// 		{
// 			connection.Close();
// 			return false;
// 		}
// 	}
// 	//更新留言信息
// 	public Boolean Update(LeaveWord leaveWord)
// 	{
// 		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
// 		try
// 		{
// 			String commandString =
// 				"update LeaveWords set" +
// 					"author=@author,"+
// 					"title=@title,"+
// 					"content=@content,"+
// 					"createTime=@createTime ;" + 
//                     "where id = @id";
// 			SqlCommand cmd = new SqlCommand(commandString, connection);
// 			connection.Open();
// 			cmd.Parameters.AddWithValue("@author", leaveWord.Author);
// 			cmd.Parameters.AddWithValue("@title", leaveWord.Title);
// 			cmd.Parameters.AddWithValue("@content", leaveWord.Content);
// 			cmd.Parameters.AddWithValue("@createTime", leaveWord.CreateTime);
//             cmd.Parameters.AddWithValue("@id", leaveWord.ID);
// 
// 			Int32 effectRow = cmd.ExecuteNonQuery();
// 			connection.Close();
// 			return effectRow > 0;
// 		}
// 		catch (System.Exception e)
// 		{
// 			connection.Close();
// 			return false;
// 		}
// 	}
// 	//删除ID为id的留言
// 	public Boolean  DeleteLeaveWord(Int32 id)
// 	{
// 		return DeleteByID("LeaveWords", id);
// 	}
// 	//
// 	public List<LeaveWord> GetLeaveWords(Int32 start, Int32 maxCount)
// 	{
// 		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
// 		try
// 		{
// 			String commandString = 
//                 "select top " + maxCount + " * from LeaveWords where id not in (" +
//                 "select top " + start + " id from LeaveWords )";//TODO:start, maxCount
// 			SqlCommand cmd = new SqlCommand(commandString, connection);
// 			connection.Open();
// 			SqlDataReader dr = cmd.ExecuteReader();
// 			List<LeaveWord> leaveWords = null;
// 			if (dr.Read())
// 			{
// 				leaveWords = new List<LeaveWord>();
// 				do{
// 					LeaveWord curLW = new LeaveWord(
// 						dr.GetInt32(0),//id
// 						dr.GetString(1),//autor
// 						dr.GetString(2),//title
// 						dr.GetString(3),//content
// 						dr.GetDateTime(4).ToString()//createTime
// 						);
// 					leaveWords.Add(curLW);
// 				}while (dr.Read());
// 			}
// 			connection.Close();
// 			return leaveWords;
// 		}
// 		catch (System.Exception e)
// 		{
// 			connection.Close();
// 			return null;
// 		}
// 	}

#endregion

#region 关键词管理
	//添加关键词
	public Boolean Insert(HotWord hotWord)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString =
				"if exists (select * from HotWords where kw=@kw)" +
					"update HotWords set t=@time,c=c+"+hotWord.AccessCount+" where kw=@kw" +
				"else " +
					"insert into HotWords values(@kw,"+hotWord.AccessCount+",@time)";
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			cmd.Parameters.AddWithValue("@kw", hotWord.Keyword);
			cmd.Parameters.AddWithValue("@time", hotWord.LastAccessTime);
			Int32 effectRow = cmd.ExecuteNonQuery();
			connection.Close();
			return effectRow > 0;
		}
		catch (System.Exception e)
		{
			connection.Close();
			return false;
		}
	}
	//删除关键词所有记录
	public Boolean DeleteHotWord(String keyword)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString = "delete from HotWords where kw = @kw";
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			cmd.Parameters.AddWithValue("@kw", keyword);
			Int32 effectRow = cmd.ExecuteNonQuery();
			connection.Close();
			return effectRow > 0;
		}
		catch (System.Exception)
		{
			connection.Close();
			return false;
		}
	}
	//取出最后访问时间在startTime后的所有词汇访问统计排名前topCount的关键词记录
	public List<HotWord> GetHotWords(DateTime startTime, Int32 topCount)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString = "select top "+topCount+" * from HotWords where t>=@time order by t";
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			cmd.Parameters.AddWithValue("@time", startTime);
			SqlDataReader dr = cmd.ExecuteReader();
			List<HotWord> hotWords = null;
			if (dr.Read())
			{
				hotWords = new List<HotWord>();
				do
				{
					HotWord curHW = new HotWord(
						dr.GetString(0),//keyword
						dr.GetInt32(1),//access count
						dr.GetDateTime(2).ToString()//acess time
						);
					hotWords.Add(curHW);
				} while (dr.Read());
			}
			connection.Close();
			return hotWords;
		}
		catch (System.Exception e)
		{
			connection.Close();
			return null;
		}
	}
#endregion

#region 系统管理
	public SystemConfigure GetSystemConfigure()
	{
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
            String commandString =
                "select * from SystemConfigures";
            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            SystemConfigure sc = null;
            if (dr.Read())
            {
                sc =
                   new SystemConfigure(new Int32 [] { dr.GetInt32(1),
                                                dr.GetInt32(2),
                                                dr.GetInt32(3),
                                                dr.GetInt32(4)},
                                       new Int32 [] { dr.GetInt32(5),
                                                dr.GetInt32(6),
                                                dr.GetInt32(7),
                                                dr.GetInt32(8)},
                                       (FounctionType)dr.GetInt32(0));
            }
            connection.Close();
            return sc;
            
        }
        catch (System.Exception e)
        {
            connection.Close();
            return null;
        }

	}
	public Boolean UpdateSystemConfigure(SystemConfigure cfg)
	{
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
            String commandString =
                "update SystemConfigures set "+
                "functionStatus=@functionStatus,"+
                "ulPublicPoint=@ulPublicPoint,"+
                "ulSharePoint=@ulSharePoint,"+
                "ulPrivatePoint=@ulPrivatePoint,"+
                "ulSafePoint=@ulSafePoint,"+
                "dlPublicPoint=@dlPublicPoint,"+
                "dlSharePoint=@dlSharePoint,"+
                "dlPrivatePoint=@dlPrivatePoint,"+
                "dlSafePoint=@dlSafePoint";
            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();

            cmd.Parameters.AddWithValue("@functionStatus",(Int32)cfg.SystemFunction);

            cmd.Parameters.AddWithValue("@ulPublicPoint",cfg.UploadArchivePoint[0]);
            cmd.Parameters.AddWithValue("@ulSharePoint",cfg.UploadArchivePoint[1]);
            cmd.Parameters.AddWithValue("@ulPrivatePoint",cfg.UploadArchivePoint[2]);
            cmd.Parameters.AddWithValue("@ulSafePoint",cfg.UploadArchivePoint[3]);

            cmd.Parameters.AddWithValue("@dlPublicPoint",cfg.DownloadArchivePoint[0]);
            cmd.Parameters.AddWithValue("@dlSharePoint",cfg.DownloadArchivePoint[1]);
            cmd.Parameters.AddWithValue("@dlPrivatePoint",cfg.DownloadArchivePoint[2]);
            cmd.Parameters.AddWithValue("@dlSafePoint",cfg.DownloadArchivePoint[3]);


            Int32 effectRows = cmd.ExecuteNonQuery();
            connection.Close();
            return effectRows > 0;
        }
        catch (System.Exception e)
        {
            connection.Close();
            return false;
        }

	}
#endregion

#region BBS
	//添加主题,topic.ID没有更新
	public Boolean Insert(Topic topic, Reply reply)
	{
		using (SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING))
		{
			connection.Open();
			SqlCommand cmd = connection.CreateCommand();
			SqlTransaction transaction = connection.BeginTransaction();
			cmd.Transaction = transaction;
			try
			{
				String cmdText =
					"insert into Topics(title,author,vc,rc,lrAuthor,lrTime, state,t)" +
					"  values(@title,@author,@vc,@rc, @lrAuthor,@lrTime, @state,@t);" +
					"declare @newID int;" +
					"set @newID=(select SCOPE_IDENTITY());" +
					"insert into Replys(id,tID,title,author,ct,cont,state)" +
					" values(@rID,@newID,@rTitle,@rAuthor,@rCT,@cont,@rState ) ";
				cmd.CommandText=cmdText;
				
				cmd.Parameters.AddWithValue("@title", topic.Title);
				cmd.Parameters.AddWithValue("@author", topic.Author);
				cmd.Parameters.AddWithValue("@vc", topic.ViewCount);
				cmd.Parameters.AddWithValue("@rc", topic.ReplyCount);
				cmd.Parameters.AddWithValue("@lrAuthor", topic.LastReplyAuthor);
				cmd.Parameters.AddWithValue("@lrTime", topic.LastReplyTime);
                cmd.Parameters.AddWithValue("@state", topic.State);
                cmd.Parameters.AddWithValue("@t", topic.Top);

				cmd.Parameters.AddWithValue("@rID", reply.ReplyID);
				cmd.Parameters.AddWithValue("@rTitle", reply.Title);
				cmd.Parameters.AddWithValue("@rAuthor", reply.Author);
				cmd.Parameters.AddWithValue("@rCT", reply.CreateTime);
				cmd.Parameters.AddWithValue("@cont", reply.Content);
				cmd.Parameters.AddWithValue("@rState", reply.State);

				Int32 effectRow = cmd.ExecuteNonQuery();
				transaction.Commit();
				return effectRow > 0;
			}
			catch (System.Exception e)
			{
				transaction.Rollback();
				return false;
			}
		}
		return false;
	}
	//添加回复
	public Boolean Insert(Reply reply)
	{
		using (SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING))
		{
			connection.Open();
			SqlCommand cmd = connection.CreateCommand();
			SqlTransaction transaction = connection.BeginTransaction();
			//cmd.Connection = connection;
			cmd.Transaction = transaction;
			try
			{
				String commandText =
					"declare @nid int;" +
					"set @nid=(select max(id) from Replys where tID=" + reply.TopicID + ")+1;" +
					"insert into Replys(id,tID,title,author,ct,cont,state) " +
						"values(@nid,"+reply.TopicID+",@title,@author,@ct,@cont,"+(Int32)reply.State+"); "+
					"update Topics set rc=rc+1,lrAuthor=@author,lrTime=@ct where id="+reply.TopicID+
					";select @nid;";
				cmd.CommandText=commandText;
				cmd.Parameters.AddWithValue("@title", reply.Title);
				cmd.Parameters.AddWithValue("@author", reply.Author);
				cmd.Parameters.AddWithValue("@ct", reply.CreateTime);
				cmd.Parameters.AddWithValue("@cont", reply.Content);

				reply.ReplyID = Convert.ToInt32(cmd.ExecuteScalar());
				transaction.Commit();
				return true;
			}
			catch (Exception e)
			{
				transaction.Rollback();
				throw e;
			}
		}
		return false;
	}
	//删除ID为id的主题（包括所有回复）
	public Boolean DeleteTopic(Int32 id)
	{
		using (SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING))
		{
			connection.Open();
			SqlCommand cmd = connection.CreateCommand();
			SqlTransaction transaction = connection.BeginTransaction();
			cmd.Transaction = transaction;
			try
			{
				String commandText =
// 					"delete from Reply where tID = "+id+";"+
 							"delete from Topics where id = "+id;
				cmd.CommandText = commandText;
				Int32 effectRow = cmd.ExecuteNonQuery();
				transaction.Commit();
				return effectRow > 0;
			}
			catch (System.Exception e)
			{
				transaction.Rollback();
				return false;
			}
		}
		return false;
	}
	//删除ID为topicID的主题的replyID回复
	public Boolean DeleteReply(Int32 topicID, Int32 replyID)
	{
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
			String cmdText = "delete from Replys where id = " + replyID + " and tID =" + topicID;
			SqlCommand cmd = new SqlCommand(cmdText, connection);
            connection.Open();
            Int32 effectRow = cmd.ExecuteNonQuery();
            connection.Close();
            return effectRow > 0;
        }
        catch (System.Exception e)
        {
            connection.Close();
            return false;
        }
	}
	//返回所有主题数目
	public Int32 GetTopicsCount()
	{
        SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
        try
        {
            String commandString = "select count(id) from Topics " ;
            SqlCommand cmd = new SqlCommand(commandString, connection);
            connection.Open();
            Object count = cmd.ExecuteScalar();
            connection.Close();
			return Convert.ToInt32(count);
        }
        catch (System.Exception e)
        {
            connection.Close();
            return 0;
        }
	}
	//返回从第start开始的最多maxCount个主题
	public List<Topic> GetTopics(Int32 start, Int32 maxCount)
	{
		using (SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING))
		{
			connection.Open();
			SqlCommand cmd = connection.CreateCommand();
			String cmdText =
				"select top " + maxCount + " * from Topics" +
					" where id not in (select top " + start + " id from Topics order by t desc,lrTime desc)  order by t desc,lrTime desc";	
			cmd.CommandText=cmdText;
			SqlDataReader dr = cmd.ExecuteReader();
			List<Topic> topicList = null;
			if (dr.Read())
			{
				topicList = new List<Topic>();
				do
				{
					Topic topic = new Topic(
						dr.GetInt32(0),//id
						dr.GetString(1),//title
						dr.GetString(2),//author
						dr.GetInt32(3),//viewCount
						dr.GetInt32(4),//replyCount
						dr.GetDateTime(6).ToString(),//lastReplyTime
						dr.GetString(5),//author
						(TopicState)dr.GetInt32(7),
                        dr.GetBoolean(8)
						);
					topicList.Add(topic);
				} while (dr.Read());
			}
			return topicList;
		}
	}
	//返回所有与主题topicID相关的回复数目
	public Int32 GetReplysCount(Int32 topicID)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString = "select count(id) from Replys where tID="+topicID;
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			Object count = cmd.ExecuteScalar();
			connection.Close();
			return Convert.ToInt32(count);
		}
		catch (System.Exception e)
		{
			connection.Close();
			return 0;
		}
	}
	//对所有与主题topicID相关的回复，返回从第start开始的最多maxCount个回复
	public List<Reply> GetReplys(Int32 topicID,Int32 start, Int32 maxCount)
	{
		using (SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING))
		{
			connection.Open();
			SqlCommand cmd = connection.CreateCommand();
			String cmdText =
				"update Topics set vc=vc+1 where id="+topicID+";"+
				"select top " + maxCount + " * from Replys" +
					" where id not in (select top " + start + " id from Replys where tID=" + topicID + " order by id)"+
					"and tID="+topicID+"  order by id";
			cmd.CommandText = cmdText;
			SqlDataReader dr = cmd.ExecuteReader();
			List<Reply> replyList = null;
			if (dr.Read())
			{
				replyList = new List<Reply>();
				do
				{
					Reply reply = new Reply(
						dr.GetString(3),//author
						dr.GetInt32(1),//topicID
						dr.GetInt32(0),//replyID
						dr.GetString(2),//title
						dr.GetString(5),//content
						dr.GetDateTime(4).ToString(),//Create Time
						(ReplyState)dr.GetInt32(6)
						);
					replyList.Add(reply);
				} while (dr.Read());
			}
			return replyList;
		}
	}
	//更新主题
	public Boolean UpdateTopic(Topic topic)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString =
				"update  Topics set " +
					"state="+(Int32)topic.State+
					",t="+(topic.Top?1:0)+" where id="+topic.ID;
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			Int32 effectRow = cmd.ExecuteNonQuery();
			connection.Close();
			return effectRow > 0;
		}
		catch (System.Exception e)
		{
			connection.Close();
			return false;
		}
	}
	//获得主题
	public Topic GetTopic(Int32 id)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			String commandString = "select * from Topics where id=" + id;
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			SqlDataReader dr = cmd.ExecuteReader();
			Topic topic = null;
			if (dr.Read())
			{
				topic = new Topic(
					dr.GetInt32(0),//id
					dr.GetString(1),//title
					dr.GetString(2),//author
					dr.GetInt32(3),//viewCount
					dr.GetInt32(4),//replyCount
					dr.GetDateTime(6).ToString(),//lastReplyTime
					dr.GetString(5),//author
					(TopicState)dr.GetInt32(7),
					dr.GetBoolean(8)
					);
			}
			connection.Close();
			return topic;
		}
		catch (System.Exception e)
		{
			connection.Close();
			return null;
		}
	}
#endregion

#region 帮助函数
	private Boolean DeleteByID(String tableName,Int32 id)
	{
		SqlConnection connection = new SqlConnection(DAMS.CONNECTIONSTRING);
		try
		{
			//tableName应不含有特殊字符，此处不用考虑注入式攻击
			String commandString = "delete from "+tableName+" where id = "+id;
			SqlCommand cmd = new SqlCommand(commandString, connection);
			connection.Open();
			Int32 effectRow = cmd.ExecuteNonQuery();
			connection.Close();
			return effectRow > 0;
		}
		catch (System.Exception)
		{
			connection.Close();
			return false;
		}
	}
#endregion
}
